﻿using System;
using System.Collections.Generic;
using System.Text;
using RFIDStockManagement.Common.Public;
using RFIDStockManagement.Model.Public;
using RFIDStockManagement.IDAL;
using System.Data.Common;

namespace RFIDStockManagement.DAL
{
    public class InventoryAccess : IInventroyManage
    {
        private DbHelper _db;
        private const string TMP_DETAILTABLEREPLACE ="{0}";

        private static string SqlStart = string.Empty;

        public InventoryAccess()
        {
            _db = new DbHelper();
        }


        /// <summary>
        /// 更改及时库存
        /// </summary>
        /// <param name="datas">库存信息</param>
        /// <param name="transA">事务</param>
        /// <param name="isAdd">是否增加库存</param>
        /// <param name="errString">出错时的提示信息</param>
        /// <returns></returns>
        public bool ChangeInventroy(List<InventoryData> datas, object transA,bool isAdd,  out string errString)
        {
            Trans tran = transA as Trans;
            DbCommand cmd;
            string detailTableName = "#Inventory_chg_" + Guid.NewGuid().ToString();
            detailTableName = detailTableName.Replace("-", "_");
            string sumTableName = "#Inventory_sum_" + Guid.NewGuid().ToString();
            sumTableName = sumTableName.Replace("-", "_");
            StringBuilder sql = new StringBuilder() ;

            errString = string.Empty;
            BuildStart(sql);
            BuildInsertValues(datas, sql,isAdd);
            BuildRepareDetailInfos(sql);
            ChangeInventoryInfos(sql);

            try
            {
                cmd = _db.GetSqlStringCommand(string.Format(sql.ToString(), detailTableName, sumTableName));

                if (tran != null)
                {
                    _db.ExecuteNonQuery(cmd, tran);
                }
                else
                {
                    _db.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                errString = ex.Message;
                return false;
            }

            return true;
        }

        private void ChangeInventoryInfos(StringBuilder sql)
        {
            //更新及时库存中已有的条目
            sql.AppendLine("Update t1 Set FQty = Case When t1.FQty + t2.FQty >0 Then t1.FQty + t2.FQty Else 0 End");
            sql.AppendLine("From rfid_Inventory t1");
            sql.AppendLine("Inner Join {1} t2 on t1.FItemID = t2.FItemID And t1.FStockID = t2.FStockID");

            //插入及时库存中不存在的条目
            sql.AppendLine("Insert Into rfid_Inventory(FitemID,FStockID,FQty)");
            sql.AppendLine("Select t1.FitemID,t1.FStockID,Case When t1.FQty >=0 Then t1.FQty Else 0 End");
            sql.AppendLine("From {1} t1");
            sql.AppendLine("Where Not Exists(select 1 From rfid_Inventory tt2 Where t1.FitemID = tt2.FItemID And t1.FStockID = tt2.FStockID)");
            sql.AppendLine("Drop table {0}");
            sql.AppendLine("Drop table {1}");

        }

        private void BuildRepareDetailInfos(StringBuilder sql)
        {
            sql.AppendLine("Update t1 set FQty = t1.FQty * t2.FRate From {0} t1 Inner Join rfid_Measure t2 on t1.FUnitID = t2.FID");
            sql.AppendLine("Insert Into {1}(FitemID,FStockID,FQty)");
            sql.AppendLine("Select t1.FItemID,t1.FStockID,Sum(t1.FQty)");
            sql.AppendLine("From {0} t1 Group by t1.FItemID,t1.FStockID");
        }

        protected void BuildInsertValues(List<InventoryData> datas, StringBuilder sql,bool isAdd)
        {
            string insertSql = "Insert Into {0}(FitemID,FStockID,FUnitID,FQty)\r\nValues(";
            foreach (InventoryData x in datas)
            {
                sql.Append(insertSql);
                sql.Append(PublicFunction.AddToValueWithCom(x.FItemID));
                sql.Append(PublicFunction.AddToValueWithCom(x.FStockId));
                sql.Append(PublicFunction.AddToValueWithCom(x.FUnitID));
                sql.Append(isAdd ? x.FQty.ToString() : (x.FQty * -1).ToString());
                sql.AppendLine(")");
            }
        }

        private void BuildStart(StringBuilder sql)
        {
            if (string.IsNullOrEmpty(SqlStart))
            {
                sql.AppendLine("IF object_id('temp..{0}') is not null");
                sql.AppendLine("Begin");
                sql.AppendLine("	Drop Table {0}");
                sql.AppendLine("End");

                sql.AppendLine("Create Table {0}(");
                sql.AppendLine("	FitemID BigInt not null,");
                sql.AppendLine("	FStockID BigInt not null,");
                sql.AppendLine("	FUnitID BigInt not null,");
                sql.AppendLine("	FQty Decimal(23,10) not null default(0)");
                sql.AppendLine(")");


                //用于合并以后的数据
                sql.AppendLine("IF object_id('temp..{1}') is not null");
                sql.AppendLine("Begin");
                sql.AppendLine("	Drop Table {1}");
                sql.AppendLine("End");

                sql.AppendLine("Create Table {1}(");
                sql.AppendLine("	FitemID BigInt not null,");
                sql.AppendLine("	FStockID BigInt not null,");
                sql.AppendLine("	FQty Decimal(23,10) not null default(0)");
                sql.AppendLine(")");
                SqlStart = sql.ToString();
            }
            else
            {
                sql.AppendLine(SqlStart);
            }
            


        }

    }
}
